using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: daiduong19051986@gmail.com
    /// <summary>
    public class TVC_MFMOVDataAccess : Connection
    {
        #region Local Variable
        private string _strTableName = "[TVC_MFMOVPRV]";
        private string _strSPGetTransferOutName = "dbo.[procTVC_MFMOVPRV_gettransferout]";
        #endregion Local Variable


        #region Method
        public TVC_MFMOVInfo Get(string db, String Code, ref string sErr)
        {
            TVC_MFMOVInfo objEntr = new TVC_MFMOVInfo();
            string sql = @"SELECT     Code, PostCode, Type, Description, Period, TransDate, Reference, ShipmentDate, ShipmentBy, BLAWBNo, DeliveryPoint, Remark, From_, To_, Supplier, WellCode, 
                      Department, PINo, ItemCode, PartNo, SerialNo, Status, Location, Quantity, Amount, POSOContract, Budget, BudgetAccount, POValue, OtherValue1, OtherValue2, OtherValue3, Account, isLink, 
                      Hold, Average, BINNo, ANAL_M0, ANAL_M1, ANAL_M2, ANAL_M3, ANAL_M4, ANAL_M5, SUNStatus, Intransit, PostBy, PostDate, CreatedBy, ModifiedBy, CreatedDate, 
                      ModifiedDate, Verified, ConfirmDate
                FROM         TVC_MFMOV" + db + @"
                WHERE     (Code = @Code) ";

            sql += " ORDER BY Code";
            InitConnect();
            InitCommand(sql, CommandType.Text);
            AddParameter(TVC_MFMOVInfo.Field.Code.ToString(), Code);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            if (list.Rows.Count > 0)
                objEntr = new TVC_MFMOVInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public DataTable GetAll(string db, ref string sErr)
        {
            string sql = @"SELECT        Code, PostCode, Type, Description, Period, TransDate, Reference, ShipmentDate, ShipmentBy, BLAWBNo, DeliveryPoint, Remark, From_, To_, Supplier, WellCode, Department, PINo, ItemCode, PartNo, SerialNo, 
                         Status, Location, Quantity, Amount, POValue, OtherValue1, OtherValue2, OtherValue3, POSOContract, Budget, BudgetAccount, Account, isLink, Hold, Average, BINNo, ANAL_M0, ANAL_M1, ANAL_M2, ANAL_M3, ANAL_M4, ANAL_M5, 
                         SUNStatus, Intransit, PostBy, PostDate, CreatedBy, ModifiedBy, CreatedDate, ModifiedDate, Verified, ConfirmDate
            FROM            TVC_MFMOV" + db;
            InitConnect();
            InitCommand(sql);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(string db, TVC_MFMOVInfo objEntr, ref string sErr)
        {
            string sql = @"INSERT INTO TVC_MFMOV" + db + @"
                         (Code, PostCode, Type, Description, Period, TransDate, Reference, ShipmentDate, ShipmentBy, BLAWBNo, DeliveryPoint, Remark, From_, To_, Supplier, WellCode, Department, PINo, ItemCode, PartNo, SerialNo, 
                         Status, Location, Quantity, Amount, POValue, OtherValue1, OtherValue2, OtherValue3, POSOContract, Budget, BudgetAccount, Account, isLink, Hold, Average, BINNo, ANAL_M0, ANAL_M1, ANAL_M2, ANAL_M3, ANAL_M4, ANAL_M5, 
                         SUNStatus, Intransit, PostBy, PostDate, CreatedBy, ModifiedBy, CreatedDate, ModifiedDate, Verified, ConfirmDate)
            VALUES        (@Code,@PostCode,@Type,@Description,@Period,@TransDate,@Reference,@ShipmentDate,@ShipmentBy,@BLAWBNo,@DeliveryPoint,@Remark,@From_,@To_,@Supplier,@WellCode,@Department,@PINo,@ItemCode,@PartNo,@SerialNo,@Status,@Location,@Quantity,@Amount,@POValue,@OtherValue1,@OtherValue2,@OtherValue3,@POSOContract,@Budget, @BudgetAccount, @Account,@isLink,@Hold,@Average,@BINNo,@ANAL_M0,@ANAL_M1,@ANAL_M2,@ANAL_M3,@ANAL_M4,@ANAL_M5,@SUNStatus,@Intransit,@PostBy,@PostDate,@CreatedBy,@ModifiedBy,@CreatedDate,@ModifiedDate,@Verified,@ConfirmDate)";
            int ret = -1;
            openConnection();
            InitCommand(sql);
            AddParameter(TVC_MFMOVInfo.Field.Code.ToString(), objEntr.Code);
            AddParameter(TVC_MFMOVInfo.Field.PostCode.ToString(), objEntr.PostCode);
            AddParameter(TVC_MFMOVInfo.Field.Type.ToString(), objEntr.Type);
            AddParameter(TVC_MFMOVInfo.Field.Description.ToString(), objEntr.Description);
            AddParameter(TVC_MFMOVInfo.Field.Period.ToString(), objEntr.Period);
            AddParameter(TVC_MFMOVInfo.Field.TransDate.ToString(), objEntr.TransDate);
            AddParameter(TVC_MFMOVInfo.Field.Reference.ToString(), objEntr.Reference);
            AddParameter(TVC_MFMOVInfo.Field.ShipmentDate.ToString(), objEntr.ShipmentDate);
            AddParameter(TVC_MFMOVInfo.Field.ShipmentBy.ToString(), objEntr.ShipmentBy);
            AddParameter(TVC_MFMOVInfo.Field.BLAWBNo.ToString(), objEntr.BLAWBNo);
            AddParameter(TVC_MFMOVInfo.Field.DeliveryPoint.ToString(), objEntr.DeliveryPoint);
            AddParameter(TVC_MFMOVInfo.Field.Remark.ToString(), objEntr.Remark);
            AddParameter(TVC_MFMOVInfo.Field.From_.ToString(), objEntr.From_);
            AddParameter(TVC_MFMOVInfo.Field.To_.ToString(), objEntr.To_);
            AddParameter(TVC_MFMOVInfo.Field.Supplier.ToString(), objEntr.Supplier);
            AddParameter(TVC_MFMOVInfo.Field.WellCode.ToString(), objEntr.WellCode);
            AddParameter(TVC_MFMOVInfo.Field.Department.ToString(), objEntr.Department);
            AddParameter(TVC_MFMOVInfo.Field.PINo.ToString(), objEntr.PINo);
            AddParameter(TVC_MFMOVInfo.Field.ItemCode.ToString(), objEntr.ItemCode);
            AddParameter(TVC_MFMOVInfo.Field.PartNo.ToString(), objEntr.PartNo);
            AddParameter(TVC_MFMOVInfo.Field.SerialNo.ToString(), objEntr.SerialNo);
            AddParameter(TVC_MFMOVInfo.Field.Status.ToString(), objEntr.Status);
            AddParameter(TVC_MFMOVInfo.Field.Location.ToString(), objEntr.Location);
            AddParameter(TVC_MFMOVInfo.Field.Quantity.ToString(), objEntr.Quantity);
            AddParameter(TVC_MFMOVInfo.Field.Amount.ToString(), objEntr.Amount);
            AddParameter(TVC_MFMOVInfo.Field.POValue.ToString(), objEntr.POValue);
            AddParameter(TVC_MFMOVInfo.Field.OtherValue1.ToString(), objEntr.OtherValue1);
            AddParameter(TVC_MFMOVInfo.Field.OtherValue2.ToString(), objEntr.OtherValue2);
            AddParameter(TVC_MFMOVInfo.Field.OtherValue3.ToString(), objEntr.OtherValue3);
            AddParameter(TVC_MFMOVInfo.Field.POSOContract.ToString(), objEntr.POSOContract);
            AddParameter(TVC_MFMOVInfo.Field.Budget.ToString(), objEntr.Budget);
            AddParameter(TVC_MFMOVInfo.Field.BudgetAccount.ToString(), objEntr.BudgetAccount);
            AddParameter(TVC_MFMOVInfo.Field.Account.ToString(), objEntr.Account);
            AddParameter(TVC_MFMOVInfo.Field.isLink.ToString(), objEntr.isLink);
            AddParameter(TVC_MFMOVInfo.Field.Hold.ToString(), objEntr.Hold);
            AddParameter(TVC_MFMOVInfo.Field.Average.ToString(), objEntr.Average);
            AddParameter(TVC_MFMOVInfo.Field.BINNo.ToString(), objEntr.BINNo);
            AddParameter(TVC_MFMOVInfo.Field.ANAL_M0.ToString(), objEntr.ANAL_M0);
            AddParameter(TVC_MFMOVInfo.Field.ANAL_M1.ToString(), objEntr.ANAL_M1);
            AddParameter(TVC_MFMOVInfo.Field.ANAL_M2.ToString(), objEntr.ANAL_M2);
            AddParameter(TVC_MFMOVInfo.Field.ANAL_M3.ToString(), objEntr.ANAL_M3);
            AddParameter(TVC_MFMOVInfo.Field.ANAL_M4.ToString(), objEntr.ANAL_M4);
            AddParameter(TVC_MFMOVInfo.Field.ANAL_M5.ToString(), objEntr.ANAL_M5);
            AddParameter(TVC_MFMOVInfo.Field.SUNStatus.ToString(), objEntr.SUNStatus);
            AddParameter(TVC_MFMOVInfo.Field.Intransit.ToString(), objEntr.Intransit);
            AddParameter(TVC_MFMOVInfo.Field.PostBy.ToString(), objEntr.PostBy);
            AddParameter(TVC_MFMOVInfo.Field.PostDate.ToString(), objEntr.PostDate);
            AddParameter(TVC_MFMOVInfo.Field.CreatedBy.ToString(), objEntr.CreatedBy);
            AddParameter(TVC_MFMOVInfo.Field.ModifiedBy.ToString(), objEntr.ModifiedBy);
            AddParameter(TVC_MFMOVInfo.Field.CreatedDate.ToString(), objEntr.CreatedDate);
            AddParameter(TVC_MFMOVInfo.Field.ModifiedDate.ToString(), objEntr.ModifiedDate);
            AddParameter(TVC_MFMOVInfo.Field.Verified.ToString(), objEntr.Verified);
            AddParameter(TVC_MFMOVInfo.Field.ConfirmDate.ToString(), objEntr.ConfirmDate);
            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(string db, TVC_MFMOVInfo objEntr, bool isamt = true)
        {
            string sql = @"UPDATE       TVC_MFMOV" + db + @"
            SET                PostCode = @PostCode, Type = @Type, Description = @Description, Period = @Period, TransDate = @TransDate, Reference = @Reference, ShipmentDate = @ShipmentDate, ShipmentBy = @ShipmentBy, 
                         BLAWBNo = @BLAWBNo, DeliveryPoint = @DeliveryPoint, Remark = @Remark, From_ = @From_, To_ = @To_, Supplier = @Supplier, WellCode = @WellCode, Department = @Department, PINo = @PINo, 
                         ItemCode = @ItemCode, PartNo = @PartNo, SerialNo = @SerialNo, Status = @Status, Location = @Location, POSOContract = @POSOContract, Budget = @Budget, BudgetAccount = @BudgetAccount, Account = @Account, isLink = @isLink, Hold = @Hold, BINNo = @BINNo, 
                         ANAL_M0 = @ANAL_M0, ANAL_M1 = @ANAL_M1, ANAL_M2 = @ANAL_M2, ANAL_M3 = @ANAL_M3, ANAL_M4 = @ANAL_M4, ANAL_M5 = @ANAL_M5, SUNStatus = @SUNStatus, Intransit = @Intransit, 
                         PostBy = @PostBy, PostDate = @PostDate, ModifiedDate = @ModifiedDate, Verified = @Verified, ConfirmDate = @ConfirmDate, ";
            if (isamt) sql += "  OtherValue2 = @OtherValue2, OtherValue3 = @OtherValue3, Quantity = @Quantity, Amount = @Amount, POValue = @POValue, OtherValue1 = @OtherValue1, Average = @Average,";
            sql += " ModifiedBy = @ModifiedBy ";
            sql += " WHERE        (Code = @Code)";
            openConnection();
            InitCommand(sql);
            AddParameter(TVC_MFMOVInfo.Field.Code.ToString(), objEntr.Code);
            AddParameter(TVC_MFMOVInfo.Field.PostCode.ToString(), objEntr.PostCode);
            AddParameter(TVC_MFMOVInfo.Field.Type.ToString(), objEntr.Type);
            AddParameter(TVC_MFMOVInfo.Field.Description.ToString(), objEntr.Description);
            AddParameter(TVC_MFMOVInfo.Field.Period.ToString(), objEntr.Period);
            AddParameter(TVC_MFMOVInfo.Field.TransDate.ToString(), objEntr.TransDate);
            AddParameter(TVC_MFMOVInfo.Field.Reference.ToString(), objEntr.Reference);
            AddParameter(TVC_MFMOVInfo.Field.ShipmentDate.ToString(), objEntr.ShipmentDate);
            AddParameter(TVC_MFMOVInfo.Field.ShipmentBy.ToString(), objEntr.ShipmentBy);
            AddParameter(TVC_MFMOVInfo.Field.BLAWBNo.ToString(), objEntr.BLAWBNo);
            AddParameter(TVC_MFMOVInfo.Field.DeliveryPoint.ToString(), objEntr.DeliveryPoint);
            AddParameter(TVC_MFMOVInfo.Field.Remark.ToString(), objEntr.Remark);
            AddParameter(TVC_MFMOVInfo.Field.From_.ToString(), objEntr.From_);
            AddParameter(TVC_MFMOVInfo.Field.To_.ToString(), objEntr.To_);
            AddParameter(TVC_MFMOVInfo.Field.Supplier.ToString(), objEntr.Supplier);
            AddParameter(TVC_MFMOVInfo.Field.WellCode.ToString(), objEntr.WellCode);
            AddParameter(TVC_MFMOVInfo.Field.Department.ToString(), objEntr.Department);
            AddParameter(TVC_MFMOVInfo.Field.PINo.ToString(), objEntr.PINo);
            AddParameter(TVC_MFMOVInfo.Field.ItemCode.ToString(), objEntr.ItemCode);
            AddParameter(TVC_MFMOVInfo.Field.PartNo.ToString(), objEntr.PartNo);
            AddParameter(TVC_MFMOVInfo.Field.SerialNo.ToString(), objEntr.SerialNo);
            AddParameter(TVC_MFMOVInfo.Field.Status.ToString(), objEntr.Status);
            AddParameter(TVC_MFMOVInfo.Field.Location.ToString(), objEntr.Location);
            if (isamt)
            {
                AddParameter(TVC_MFMOVInfo.Field.Quantity.ToString(), objEntr.Quantity);
                AddParameter(TVC_MFMOVInfo.Field.Amount.ToString(), objEntr.Amount);
                AddParameter(TVC_MFMOVInfo.Field.POValue.ToString(), objEntr.POValue);
                AddParameter(TVC_MFMOVInfo.Field.OtherValue1.ToString(), objEntr.OtherValue1);
                AddParameter(TVC_MFMOVInfo.Field.OtherValue2.ToString(), objEntr.OtherValue2);
                AddParameter(TVC_MFMOVInfo.Field.OtherValue3.ToString(), objEntr.OtherValue3);
                AddParameter(TVC_MFMOVInfo.Field.Average.ToString(), objEntr.Average);
            }
            AddParameter(TVC_MFMOVInfo.Field.POSOContract.ToString(), objEntr.POSOContract);
            AddParameter(TVC_MFMOVInfo.Field.Budget.ToString(), objEntr.Budget);
            AddParameter(TVC_MFMOVInfo.Field.BudgetAccount.ToString(), objEntr.BudgetAccount);
            AddParameter(TVC_MFMOVInfo.Field.Account.ToString(), objEntr.Account);
            AddParameter(TVC_MFMOVInfo.Field.isLink.ToString(), objEntr.isLink);
            AddParameter(TVC_MFMOVInfo.Field.Hold.ToString(), objEntr.Hold);

            AddParameter(TVC_MFMOVInfo.Field.BINNo.ToString(), objEntr.BINNo);
            AddParameter(TVC_MFMOVInfo.Field.ANAL_M0.ToString(), objEntr.ANAL_M0);
            AddParameter(TVC_MFMOVInfo.Field.ANAL_M1.ToString(), objEntr.ANAL_M1);
            AddParameter(TVC_MFMOVInfo.Field.ANAL_M2.ToString(), objEntr.ANAL_M2);
            AddParameter(TVC_MFMOVInfo.Field.ANAL_M3.ToString(), objEntr.ANAL_M3);
            AddParameter(TVC_MFMOVInfo.Field.ANAL_M4.ToString(), objEntr.ANAL_M4);
            AddParameter(TVC_MFMOVInfo.Field.ANAL_M5.ToString(), objEntr.ANAL_M5);
            AddParameter(TVC_MFMOVInfo.Field.SUNStatus.ToString(), objEntr.SUNStatus);
            AddParameter(TVC_MFMOVInfo.Field.Intransit.ToString(), objEntr.Intransit);
            AddParameter(TVC_MFMOVInfo.Field.PostBy.ToString(), objEntr.PostBy);
            AddParameter(TVC_MFMOVInfo.Field.PostDate.ToString(), objEntr.PostDate);

            AddParameter(TVC_MFMOVInfo.Field.ModifiedBy.ToString(), objEntr.ModifiedBy);

            AddParameter(TVC_MFMOVInfo.Field.ModifiedDate.ToString(), objEntr.ModifiedDate);
            AddParameter(TVC_MFMOVInfo.Field.Verified.ToString(), objEntr.Verified);
            AddParameter(TVC_MFMOVInfo.Field.ConfirmDate.ToString(), objEntr.ConfirmDate);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(string db, String Code)
        {
            string sql = @"DELETE TVC_MFMOV" + db + @"
            WHERE Code = @Code";
            openConnection();
            InitCommand(sql);
            AddParameter(TVC_MFMOVInfo.Field.Code.ToString(), Code);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public Boolean IsExist(string db, String Code)
        {
            string sql = @"SELECT        TOP (1) Code
            FROM            TVC_MFMOV" + db + @"
            WHERE        (Code = @Code)";
            InitConnect();
            InitCommand(sql);
            AddParameter(TVC_MFMOVInfo.Field.Code.ToString(), Code);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(TVC_MFMOVInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            InitConnect();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }

            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            InitConnect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method


        public DataTable GetBalance(string db, string ItemCode, ref string sErr)
        {
            string sql = @"SELECT        ROUND(SUM(Quantity), 5) AS UnitInStock, ROUND(SUM(Amount), 5) AS SumAmount, ItemCode
            FROM            TVC_MFMOV" + db + @"
            WHERE        (PostCode <> 'Z') AND (ItemCode = @ItemCode)
            GROUP BY ItemCode";
            InitConnect();
            InitCommand(sql);
            AddParameter("ItemCode", ItemCode);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public bool ItemCodeIsExist(string db, string ItemCode)
        {
            string sql = @"SELECT        TOP (1) ItemCode
            FROM            TVC_MFMOV" + db + @"
            WHERE        (ItemCode = @Itemcode)";
            InitConnect();
            InitCommand(sql);
            AddParameter(TVC_MFMOVInfo.Field.ItemCode.ToString(), ItemCode);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count >= 1)
                return true;
            return false;
        }
        public string DeleteRef(string db, String Reference)
        {
            string sql = @"DELETE FROM TVC_MFMOV" + db + @"
            WHERE        (Reference = @Reference)";
            openConnection();
            InitCommand(sql);
            AddParameter(TVC_MFMOVInfo.Field.Reference.ToString(), Reference);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }
        public DataTable GetbyReference(string db, string Reference, string isHold, ref string sErr)
        {
            string sql = @"SELECT     Code, PostCode, Type, Description, Period, TransDate, Reference, ShipmentDate, ShipmentBy, BLAWBNo, DeliveryPoint, Remark, From_, To_, Supplier, WellCode, 
                      Department, PINo, ItemCode, PartNo, SerialNo, Status, Location, Quantity, Amount, POSOContract, Budget, BudgetAccount, POValue, OtherValue1, OtherValue2, OtherValue3, Account, isLink, 
                      Hold, Average, BINNo, ANAL_M0, ANAL_M1, ANAL_M2, ANAL_M3, ANAL_M4, ANAL_M5, SUNStatus, Intransit, PostBy, PostDate, CreatedBy, ModifiedBy, CreatedDate, 
                      ModifiedDate, Verified, ConfirmDate
                FROM         TVC_MFMOV" + db + @"
                WHERE     (Reference = @Reference) ";
            if (isHold != null) sql += " AND PostCode = 'Z'";

            sql += " ORDER BY Code";
            InitConnect();
            InitCommand(sql, CommandType.Text);
            AddParameter(TVC_MFMOVInfo.Field.Reference.ToString(), Reference);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetbyReferenceList(string db, string Reference, string isHold, ref string sErr)
        {
            string sql = @"SELECT * 
            FROM (SELECT        m.Code, m.PostCode, m.Type, m.Description, m.Period, m.TransDate, m.Reference, m.ShipmentDate, m.ShipmentBy, m.BLAWBNo, m.DeliveryPoint, m.Remark, m.From_, m.To_, m.Supplier, m.WellCode, 
                         m.Department, m.PINo, m.ItemCode, m.PartNo, m.SerialNo, m.Status, m.Location, m.Quantity, m.Amount, m.POSOContract, m.Budget, m.BudgetAccount, m.Account, m.isLink, m.Hold, m.Average, m.BINNo, m.ANAL_M0, m.ANAL_M1, 
                         m.ANAL_M2, m.ANAL_M3, m.ANAL_M4, m.ANAL_M5, m.SUNStatus, m.PostBy, m.PostDate, m.CreatedBy, m.ModifiedBy, m.CreatedDate, m.ModifiedDate, m.POValue, m.OtherValue1, m.OtherValue2, 
                         m.OtherValue3, m.Intransit, m.Verified, m.ConfirmDate, i.Description AS ItemDesc
                FROM            TVC_RFITM" + db + @" AS i 
                    RIGHT OUTER JOIN TVC_MFMOV" + db + @" AS m ON i.ItemCode = m.ItemCode
                WHERE (m.Reference = @Reference)) AS M
            WHERE     (Reference = @Reference) ";
            if (isHold != null) sql += " AND PostCode = 'Z'";

            sql += " ORDER BY Code";
            InitConnect();
            InitCommand(sql, CommandType.Text);
            AddParameter(TVC_MFMOVInfo.Field.Reference.ToString(), Reference);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public TVC_MFMOVInfo GetbyItemCode(string db, string ItemCode, ref string sErr)
        {
            string sql = @"SELECT        TOP (1) Code, PostCode, Type, Description, Period, TransDate, Reference, ShipmentDate, ShipmentBy, BLAWBNo, DeliveryPoint, Remark, From_, To_, Supplier, WellCode, Department, PINo, ItemCode, PartNo, 
                         SerialNo, Status, Location, Quantity, Amount, POSOContract, Budget, BudgetAccount, POValue, OtherValue1, OtherValue2, OtherValue3, Account, isLink, Hold, Average, BINNo, ANAL_M0, ANAL_M1, ANAL_M2, ANAL_M3, ANAL_M4, 
                         ANAL_M5, SUNStatus, Intransit, PostBy, PostDate, CreatedBy, ModifiedBy, CreatedDate, ModifiedDate, Verified, ConfirmDate
            FROM            TVC_MFMOV" + db + @"
            WHERE        (ItemCode = @ItemCode)";
            TVC_MFMOVInfo objEntr = new TVC_MFMOVInfo();
            InitConnect();
            InitCommand(sql);
            AddParameter(TVC_MFMOVInfo.Field.ItemCode.ToString(), ItemCode);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (list.Rows.Count > 0)
                objEntr = new TVC_MFMOVInfo(list.Rows[0]);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public DataTable GetHold(string db, string Hold, ref string sErr)
        {
            string sql = @"SELECT        Type, Description, Period, CONVERT(Datetime, CONVERT(nvarchar(10), TransDate, 103), 103) AS TransDate, Reference, CONVERT(Datetime, CONVERT(nvarchar(10), ShipmentDate, 103), 103) AS ShipmentDate, 
                         ShipmentBy, BLAWBNo, DeliveryPoint, Remark, From_, To_, Supplier, WellCode, Department, SUM(Quantity) AS SQuantity, SUM(Amount) AS SAmount, POSOContract, Budget, BudgetAccount, Account, isLink, Hold, SUNStatus, Verified, 
                         ConfirmDate
            FROM            TVC_MFMOV" + db + @"
            WHERE        ([Hold] = @Hold OR isnull(@Hold,'All') <> 'Y')
            GROUP BY Type, Description, Period, TransDate, Reference, ShipmentDate, ShipmentBy, BLAWBNo, DeliveryPoint, Remark, From_, To_, Supplier, WellCode, Department, POSOContract, Budget, BudgetAccount, Account, isLink, Hold, SUNStatus, 
                         Verified, ConfirmDate";
            TVC_MFMOVInfo objEntr = new TVC_MFMOVInfo();
            InitConnect();
            InitCommand(sql);
            AddParameter(TVC_MFMOVInfo.Field.Hold.ToString(), Hold);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }

            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetQuery(string query, ref string sErr)
        {
            InitConnect();
            //object list;
            DataTable list = new DataTable();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }


            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetbyFilter(string db, string strFilter, ref string sErr)
        {
            string sql = @"SELECT * 
            FROM (SELECT        m.Code, m.PostCode, m.Type, m.Description, m.Period, m.TransDate, m.Reference, m.ShipmentDate, m.ShipmentBy, m.BLAWBNo, m.DeliveryPoint, m.Remark, m.From_, m.To_, m.Supplier, m.WellCode, 
                         m.Department, m.PINo, m.ItemCode, m.PartNo, m.SerialNo, m.Status, m.Location, m.Quantity, m.Amount, m.POSOContract, m.Budget, m.BudgetAccount, m.Account, m.isLink, m.Hold, m.Average, m.BINNo, m.ANAL_M0, m.ANAL_M1, 
                         m.ANAL_M2, m.ANAL_M3, m.ANAL_M4, m.ANAL_M5, m.SUNStatus, m.PostBy, m.PostDate, m.CreatedBy, m.ModifiedBy, m.CreatedDate, m.ModifiedDate, m.POValue, m.OtherValue1, m.OtherValue2, 
                         m.OtherValue3, m.Intransit, m.Verified, m.ConfirmDate, i.Description AS ItemDesc
                FROM            TVC_RFITM" + db + @" AS i 
                    RIGHT OUTER JOIN TVC_MFMOV" + db + @" AS m ON i.ItemCode = m.ItemCode) AS M ";
            if (!string.IsNullOrEmpty(strFilter)) sql += " WHERE " + strFilter;
            sql += " ORDER BY PostCode, Reference, Code ASC ";
            InitConnect();
            InitCommand(sql);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }

            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetbyFilterList(string db, string strFilter, ref string sErr)
        {
            string sql = @"SELECT * 
            FROM (SELECT        m.Code, m.PostCode, m.Type, m.Description, m.Period, m.TransDate, m.Reference, m.ShipmentDate, m.ShipmentBy, m.BLAWBNo, m.DeliveryPoint, m.Remark, m.From_, m.To_, m.Supplier, m.WellCode, 
                         m.Department, m.PINo, m.ItemCode, m.PartNo, m.SerialNo, m.Status, m.Location, m.Quantity, m.Amount, m.POSOContract, m.Budget, m.BudgetAccount, m.Account, m.isLink, m.Hold, m.Average, m.BINNo, m.ANAL_M0, m.ANAL_M1, 
                         m.ANAL_M2, m.ANAL_M3, m.ANAL_M4, m.ANAL_M5, m.SUNStatus, m.PostBy, m.PostDate, m.CreatedBy, m.ModifiedBy, m.CreatedDate, m.ModifiedDate, m.POValue, m.OtherValue1, m.OtherValue2, 
                         m.OtherValue3, m.Intransit, m.Verified, m.ConfirmDate, i.Description AS ItemDesc
                FROM            TVC_RFITM" + db + @" AS i 
                    RIGHT OUTER JOIN TVC_MFMOV" + db + @" AS m ON i.ItemCode = m.ItemCode) AS M ";
            if (!string.IsNullOrEmpty(strFilter)) sql += " WHERE " + strFilter;
            sql += " ORDER BY PostCode, Reference, Code ASC ";

            InitConnect();
            InitCommand(sql, CommandType.Text);
            //AddParameter(strFilter, strFilter);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public string UpdateRef(string db, TVC_MFMOVInfo objEntr)
        {
            string sql = @"UPDATE       TVC_MFMOV" + db + @"
            SET                TransDate = @TransDate, Period = @Period, ShipmentDate = @ShipmentDate, Intransit = @Intransit, ShipmentBy = @ShipmentBy, BLAWBNo = @BLAWBNo, PINo = @PINo, Remark = @Remark, 
                         From_ = @From_, To_ = @To_, DeliveryPoint = @DeliveryPoint, POSOContract = @POSOContract, Budget = @Budget, BudgetAccount = @BudgetAccount, Supplier = @Supplier, Department = @Department, WellCode = @WellCode
            WHERE        (Reference = @Reference)";
            openConnection();
            InitCommand(sql);

            AddParameter(TVC_MFMOVInfo.Field.Reference.ToString(), objEntr.Reference);
            AddParameter(TVC_MFMOVInfo.Field.Period, objEntr.Period);
            AddParameter(TVC_MFMOVInfo.Field.TransDate, objEntr.TransDate);
            AddParameter(TVC_MFMOVInfo.Field.ShipmentDate.ToString(), objEntr.ShipmentDate);
            AddParameter(TVC_MFMOVInfo.Field.ShipmentBy.ToString(), objEntr.ShipmentBy);
            AddParameter(TVC_MFMOVInfo.Field.BLAWBNo.ToString(), objEntr.BLAWBNo);
            AddParameter(TVC_MFMOVInfo.Field.DeliveryPoint.ToString(), objEntr.DeliveryPoint);
            AddParameter(TVC_MFMOVInfo.Field.Remark.ToString(), objEntr.Remark);
            AddParameter(TVC_MFMOVInfo.Field.From_.ToString(), objEntr.From_);
            AddParameter(TVC_MFMOVInfo.Field.To_.ToString(), objEntr.To_);
            AddParameter(TVC_MFMOVInfo.Field.Supplier.ToString(), objEntr.Supplier);
            AddParameter(TVC_MFMOVInfo.Field.WellCode.ToString(), objEntr.WellCode);
            AddParameter(TVC_MFMOVInfo.Field.Department.ToString(), objEntr.Department);
            AddParameter(TVC_MFMOVInfo.Field.PINo.ToString(), objEntr.PINo);
            AddParameter(TVC_MFMOVInfo.Field.POSOContract.ToString(), objEntr.POSOContract);
            AddParameter(TVC_MFMOVInfo.Field.Budget.ToString(), objEntr.Budget);
            AddParameter(TVC_MFMOVInfo.Field.BudgetAccount.ToString(), objEntr.BudgetAccount);
            AddParameter(TVC_MFMOVInfo.Field.Intransit.ToString(), objEntr.Intransit);
            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string UpdateTransit(string db, string code, DateTime ConfirmDate, string transit)
        {
            openConnection();
            InitCommand(@"UPDATE TVC_MFMOV" + db + @"
                SET InTransit = @Transit,
                    ConfirmDate = @ConfirmDate
                WHERE   Code = @Code ", CommandType.Text);

            AddParameter("Code", code);
            AddParameter("ConfirmDate", ConfirmDate);
            AddParameter("Transit", transit);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }
        public string UpdateVerified(string db, string Reference, string Verified)
        {
            openConnection();
            InitCommand(@"UPDATE TVC_MFMOV" + db + @"
                SET Verified = @Verified
                WHERE   Reference = @Reference ", CommandType.Text);

            AddParameter("Reference", Reference);
            AddParameter("Verified", Verified);

            string sErr = "";
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public bool HasRefInPost(string db, string refer, string moveType)
        {
            InitConnect();
            InitCommand(@"SELECT TOP(1) Reference FROM TVC_MFMOV" + db + @"
                WHERE   (Reference = @Reference AND PostCode <> 'Z')", CommandType.Text);

            AddParameter("Reference", refer);
            AddParameter("Type", moveType);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex) { }
            return list.Rows.Count > 0;
        }

        public bool Has2Ref(string db, string refer, string moveType)
        {
            InitConnect();
            InitCommand(@"SELECT TOP(1) Reference FROM TVC_MFMOV" + db + @"
                WHERE (Reference = @Reference AND Type <> @Type)", CommandType.Text);

            AddParameter("Reference", refer);
            AddParameter("Type", moveType);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex) { }
            return list.Rows.Count > 0;
        }

        public DataTable GetItemSumByRef(string db, string refer, ref string sErr)
        {
            string sql = @"SELECT     ItemCode, Location, SUM(Quantity) AS Quantity, SUM(Amount) AS Amount
                FROM         TVC_MFMOV" + db + @"
                WHERE     (PostCode <> 'Z')";
            if (refer != null) sql += " AND ItemCode in (SELECT ItemCode FROM TVC_MFMOVPRV WHERE Reference=@Reference) ";
            sql += " GROUP BY ItemCode, Location ";

            InitConnect();
            InitCommand(sql, CommandType.Text);

            if (refer != null) AddParameter("Reference", refer);


            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex) { }
            return list;
        }

        public bool HasRefInUsed(string db, string refer)
        {
            InitConnect();
            InitCommand(@"SELECT TOP(1) Reference FROM TVC_MFMOV" + db + @"
                WHERE (Reference = @Reference)", CommandType.Text);

            AddParameter("Reference", refer);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex) { }
            return list.Rows.Count > 0;
        }

        public DataTable GetHeld(string db, string Held, ref string sErr)
        {
            string sql = @" SELECT Reference 
            FROM TVC_MFMOV" + db + @" 
            WHERE Hold = @Hold AND Verified <> 'Y'  
            GROUP BY Reference 
            ORDER BY Reference ";

            InitConnect();
            InitCommand(sql, CommandType.Text);

            AddParameter("Hold", Held);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex) { }
            return list;
        }

        public DataTable GetPost(string db, ref string sErr)
        {
            string sql = @" SELECT Reference, PostCode, (Reference + ' - ' + PostCode) as DisplayCBO,SUNStatus 
            FROM TVC_MFMOV" + db + @"
            WHERE  PostCode <> 'Z' AND SUNStatus = 'N'
            GROUP BY Reference, PostCode, SUNStatus             
            ORDER BY PostCode ";
            InitConnect();
            InitCommand(sql, CommandType.Text);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex) { }
            return list;
        }

        public DataTable GetVerify(string db, ref string sErr)
        {
            string sql = @" SELECT Reference 
            FROM TVC_MFMOV" + db + @"
            WHERE PostCode = 'Z' AND Verified = 'Y' 
            GROUP BY Reference 
            ORDER BY Reference ";
            InitConnect();
            InitCommand(sql, CommandType.Text);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex) { }
            return list;
        }

        public DataTable GetMaxPostCode(string db, ref string sErr)
        {
            InitConnect();

            InitCommand(@" select MAX(RIGHT([PostCode],4))  AS MaxNo 
            from TVC_MFMOV" + db + @"
            where LEFT([PostCode],4) = '" + DateTime.Now.ToString("yyMM") + "' ", CommandType.Text);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex) { }
            return list;
        }

        public DataTable GetMaxCode(string db, ref string sErr)
        {
            InitConnect();

            InitCommand(@" select MAX(RIGHT([Code],4))  AS MaxNo 
            from TVC_MFMOV" + db + " where LEFT([Code],4) = '" + DateTime.Now.ToString("yyMM") + "' ", CommandType.Text);
            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex) { }
            return list;
        }

        public DataTable GetSumQtyLocation(string db, string ItemCode, string Location, ref string sErr)
        {
            InitConnect();

            InitCommand(@" select ItemCode, isnull(Sum(Quantity),0) as Quantity 
            from TVC_MFMOV" + db + @"
            where PostCode <> 'Z' and  (((ItemCode)=@ItemCode))  and (((Location)=@Location)) 
            group by ItemCode", CommandType.Text);
            AddParameter("Location", Location);
            AddParameter("ItemCode", ItemCode);

            DataTable list = new DataTable();
            try
            {
                list = executeSelect();
            }
            catch (Exception ex) { }
            return list;
        }

        public List<string> GetReferenceNotSun(string db, ref string sErr)
        {
            List<string> res = new List<string>();
            var sql = @"SELECT Reference
            FROM            TVC_MFMOV" + db + @"
            WHERE        (PostCode <> 'Z') AND (SUNStatus IS NULL OR SUNStatus <> 'Y')";
            InitConnect();
            InitCommand(sql);
            try
            {
                var list = executeSelect();
                foreach (DataRow r in list.Rows)
                {
                    res.Add(r[0].ToString());
                }
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }

            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return res;
        }

        public List<string> GetSunReference(string db, List<string> refs, string sunConnect, ref string sErr)
        {
            var References = string.Format("'{0}'", string.Join("','", refs.ToArray()));
            string sql = @"SELECT        MVMNT_REF AS TRANS_REF
                FROM            SMMFMOV" + db + @"
                WHERE MVMNT_REF IN (" + References + ")";

            List<string> res = new List<string>();
            try
            {
                using (SqlConnection conn = new SqlConnection(sunConnect))
                {
                    using (SqlDataAdapter adapter = new SqlDataAdapter(sql, conn))
                    {
                        DataTable dt = new DataTable();
                        adapter.Fill(dt);
                        foreach (DataRow r in dt.Rows)
                        {
                            res.Add(r[0].ToString());
                        }
                    }
                }
            }
            catch (Exception ex) { sErr = ex.Message; }
            return res;
        }

        public void updSunStatus(string db, string refer, string status, ref string sErr)
        {
            var sql = @"UPDATE       TVC_MFMOV" + db + @"
            SET                SUNStatus = @SUNStatus
            WHERE      (Reference = @Reference)";
            openConnection();
            InitCommand(sql);
            AddParameter(TVC_MFMOVInfo.Field.SUNStatus, status);
            AddParameter(TVC_MFMOVInfo.Field.Reference, refer);
            try
            {
                executeNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            finally { closeConnection(); }
            if (sErr != "") ErrorLog.SetLog(sErr);
        }
    }
}
